#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Copyright (c) 2010 by Derek Anderson
#

import datetime, json, os, sqlite3, sys, textwrap, threading, time, traceback, uuid

import sqlparse


try:
  import pygtk
  pygtk.require("2.0")
  import gtk
  import gtk.glade
  import gtksourceview2
  import gobject, gnome
  import pango
except:
  traceback.print_exc()
  sys.exit(1)

from util import *
import gui_conn


class NewConn(object):
  def __init__(self,notebook, main):
    self.init_db()
    self.notebook = notebook
    self.main = main
    self.conns = main.conns
    self.xml = gtk.glade.XML( os.path.join( RUN_FROM_DIR, 'new_conn.glade' ) )
    self.window = self.xml.get_widget('connect_window')
    self.window.connect("delete-event", lambda a,b: self.window.destroy() )
    self.xml.get_widget('cancel').connect('clicked',lambda x:self.window.destroy())
    self.xml.get_widget('connect').connect('clicked',self.handle_connect_clicked)
    self.connection_type = self.xml.get_widget('connection_type')
    self.notebook_saved_conns = self.xml.get_widget('notebook_saved_conns')
    def connection_type_changed(a=None):
      s = self.connection_type.get_active_text()
      if s in ('MySQL','SQL Server'):
        self.xml.get_widget('label_server').set_visible(True)
        self.xml.get_widget('server').set_visible(True)
        self.xml.get_widget('label_port').set_visible(True)
        self.xml.get_widget('port').set_visible(True)
        self.xml.get_widget('label_username').set_visible(True)
        self.xml.get_widget('username').set_visible(True)
        self.xml.get_widget('label_password').set_visible(True)
        self.xml.get_widget('password').set_visible(True)
        self.xml.get_widget('label_schema').set_visible(True)
        self.xml.get_widget('schema').set_visible(True)
        self.xml.get_widget('label_file').set_visible(False)
        self.xml.get_widget('file').set_visible(False)
        self.xml.get_widget('label_ssh_tunnel').set_visible(True)
        self.xml.get_widget('ssh_tunnel').set_visible(True)
      elif s in ('SQLite3',):
        self.xml.get_widget('label_server').set_visible(False)
        self.xml.get_widget('server').set_visible(False)
        self.xml.get_widget('label_port').set_visible(False)
        self.xml.get_widget('port').set_visible(False)
        self.xml.get_widget('label_username').set_visible(False)
        self.xml.get_widget('username').set_visible(False)
        self.xml.get_widget('label_password').set_visible(False)
        self.xml.get_widget('password').set_visible(False)
        self.xml.get_widget('label_schema').set_visible(True)
        self.xml.get_widget('schema').set_visible(True)
        self.xml.get_widget('label_file').set_visible(True)
        self.xml.get_widget('file').set_visible(True)
        self.xml.get_widget('label_ssh_tunnel').set_visible(False)
        self.xml.get_widget('ssh_tunnel').set_visible(False)
      else:
        self.xml.get_widget('label_server').set_visible(False)
        self.xml.get_widget('server').set_visible(False)
        self.xml.get_widget('label_port').set_visible(False)
        self.xml.get_widget('port').set_visible(False)
        self.xml.get_widget('label_username').set_visible(False)
        self.xml.get_widget('username').set_visible(False)
        self.xml.get_widget('label_password').set_visible(False)
        self.xml.get_widget('password').set_visible(False)
        self.xml.get_widget('label_schema').set_visible(False)
        self.xml.get_widget('schema').set_visible(False)
        self.xml.get_widget('label_file').set_visible(False)
        self.xml.get_widget('file').set_visible(False)
        self.xml.get_widget('label_ssh_tunnel').set_visible(False)
        self.xml.get_widget('ssh_tunnel').set_visible(False)
    self.connection_type.connect('changed',connection_type_changed)
    connection_type_changed()
    try:
      import MySQLdb
      self.connection_type.append_text('MySQL')
    except:
      print 'could not load MySQLdb'
      traceback.print_exc()
    try:
      import pymssql
      self.connection_type.append_text('SQL Server')
    except:
      print 'could not load pymssql'
    try:
      #import sqlite3
      # sqlite3 doesn't work because it's threading support is so shitty
      #self.connection_type.append_text('SQLite3')
      pass
    except:
      print 'could not load sqlite3'
    
    # saved connections
    treestore = gtk.TreeStore(str,str,str,str,int,str,str,str,str,int) # repr,conn_id,db_type,server,port,username,password,schema,ssh_tunnel,count
    treestore.set_sort_column_id(9, gtk.SORT_DESCENDING)
    self.saved_conns_treeview = treeview = self.xml.get_widget('treeview')
    treeview.get_selection().set_mode(gtk.SELECTION_MULTIPLE)
    treeview.set_model(treestore)
    treeview.set_headers_visible(False)
    column = gtk.TreeViewColumn('Object')
    treeview.append_column(column)
    cell = gtk.CellRendererText()
    cell.set_property('font','monospace')
    column.pack_start(cell, True)
    column.add_attribute(cell, 'markup', 0)


    column = gtk.TreeViewColumn('Object')
    treeview.append_column(column)
    cell = gtk.CellRendererText()
    cell.set_property('font','monospace')
    column.pack_start(cell, True)
    column.add_attribute(cell, 'markup', 2)

    def row_activated(treeview, path, view_column):
      #self.handle_connect_clicked()
      #return False
      model = treeview.get_model()
      itr = model.get_iter(path)
      conn_id = model.get_value(itr,1)
      db_type = model.get_value(itr,2)
      server = model.get_value(itr,3)
      port = model.get_value(itr,4)
      username = model.get_value(itr,5)
      password = model.get_value(itr,6)
      schema = model.get_value(itr,7)
      ssh_tunnel = model.get_value(itr,8)
      count = model.get_value(itr,9)
      self.new_conn(conn_id=conn_id, db_type=db_type, server=server, port=port, username=username, password=password, schema=schema, ssh_tunnel=ssh_tunnel)
      self.window.destroy()
    treeview.connect('row-activated', row_activated)
    
    def key_press_event(treeview, event):
      #print 'woot', event, event.keyval
      if event.keyval==gtk.gdk.keyval_from_name('Delete'):
        self.delete_conns()
    treeview.connect('key_press_event',key_press_event)
    
    def button_press_event(treeview, event):
      if event.button != 3: return
      x = int(event.x)
      y = int(event.y)
      time = event.time
      menu = gtk.Menu()
      pthinfo = treeview.get_path_at_pos(x, y)
      model = treeview.get_model()
      
      # sort submenu
      item = gtk.ImageMenuItem('Sort by')
      sortby_submenu = gtk.Menu()
      item.set_submenu(sortby_submenu)
      menu.append(item)
      item_g = gtk.RadioMenuItem(label='Hostname')
      item_g.set_active(model.get_sort_column_id()[0]==3)
      item_g.connect('activate', lambda x: model.set_sort_column_id(3, gtk.SORT_ASCENDING))
      sortby_submenu.append(item_g)
      item = gtk.RadioMenuItem(group=item_g, label='Username')
      item.set_active(model.get_sort_column_id()[0]==5)
      item.connect('activate', lambda x: model.set_sort_column_id(5, gtk.SORT_ASCENDING))
      sortby_submenu.append(item)
      item = gtk.RadioMenuItem(group=item_g, label='Server type')
      item.set_active(model.get_sort_column_id()[0]==2)
      item.connect('activate', lambda x: model.set_sort_column_id(2, gtk.SORT_ASCENDING))
      sortby_submenu.append(item)
      item = gtk.RadioMenuItem(group=item_g, label='Most used')
      item.set_active(model.get_sort_column_id()[0]==9)
      item.connect('activate', lambda x: model.set_sort_column_id(9, gtk.SORT_DESCENDING))
      sortby_submenu.append(item)

      # delete
      item = gtk.ImageMenuItem('Delete')
      item.connect('activate', lambda x: self.delete_conns())
      menu.append(item)

      menu.show_all()
      menu.popup( None, None, None, event.button, event.time)
      return True
    treeview.connect('button_press_event', button_press_event)
    
    log_conn = get_log_conn()
    cur = log_conn.cursor()
    cur.execute('select conn_id, db_type, server, port, username, password, schema, ssh_tunnel, count from connections order by count desc')
    self.notebook_saved_conns.set_current_page(1)
    dbs = {}
    for row in cur:
      dbs_key = (row[1],row[2])
      row = list(row)
      try: row[3] = int(row[3])
      except: row[3] = 0
      try: row[8] = int(row[8])
      except: row[8] = 0
      if dbs_key in dbs:
        if not treestore.iter_has_child(dbs[dbs_key]):
          dup_child_row = [treestore.get_value(dbs[dbs_key],i) for i in range(treestore.get_n_columns())]
          dup_child_row[0] = 'user: %s\nschema: %s'%(dup_child_row[5],dup_child_row[7]) if dup_child_row[7] else 'user: %s'%(dup_child_row[5],)
          treestore.append(dbs[dbs_key], dup_child_row)
          s = 'server: <b>%s</b>'%(row[2]+':'+row[3] if row[3] else row[2],)
          if row[7]: s += '\nSSH tunnel: %s' % row[7]
          treestore.set_value(dbs[dbs_key],0,s)
        s = 'user: %s\nschema: %s'%(row[4],row[6]) if row[6] else 'user: %s'%(row[4],)
        treerow = [s]+[x for x in row]
        treestore.append(dbs[dbs_key], treerow)
      else:
        s = 'server: <b>%s</b>\nuser: %s'%(row[2]+':'+row[3] if row[3] else row[2],row[4])
        if row[6]: s += '\nschema: %s' % row[6]
        if row[7]: s += '\nSSH tunnel: %s' % row[7]
        treerow = [s]+[x for x in row]
        dbs[dbs_key] = treestore.append(None, treerow)
      self.notebook_saved_conns.set_current_page(0)
    #treeview.expand_all()  
    cur.close()
    
    self.window.show()

  def delete_conns(self):
    model, paths = self.saved_conns_treeview.get_selection().get_selected_rows()
    for path in paths:
      itr = model.get_iter(path)
      dialog = gtk.MessageDialog(parent=None, flags=0, type=gtk.MESSAGE_QUESTION, buttons=gtk.BUTTONS_YES_NO, message_format=None)
      dialog.set_markup('<b>Do you want to delete this database connection?</b>\n\n'+model.get_value(itr,0)+'\ntype: '+model.get_value(itr,2))
      if dialog.run()==-8:
        log_conn = get_log_conn()
        cur = log_conn.cursor()
        cur.execute('delete from connections where conn_id=:conn_id', {'conn_id':model.get_value(itr,1)})
        log_conn.commit()
        print 'deleted connection', model.get_value(itr,1)
        model.remove(itr)
      dialog.destroy()
    
  def handle_connect_clicked(self,x=None):
    if self.notebook_saved_conns.get_current_page()==0:
      model, paths = self.saved_conns_treeview.get_selection().get_selected_rows()
      for path in paths:
        itr = model.get_iter(path)
        conn_id = model.get_value(itr,1)
        db_type = model.get_value(itr,2)
        server = model.get_value(itr,3)
        port = model.get_value(itr,4)
        username = model.get_value(itr,5)
        password = model.get_value(itr,6)
        schema = model.get_value(itr,7)
        ssh_tunnel = model.get_value(itr,8)
        count = model.get_value(itr,9)
        print 'connecting to:', conn_id
        self.new_conn(conn_id=conn_id, db_type=db_type, server=server, port=port, username=username, password=password, schema=schema, ssh_tunnel=ssh_tunnel)
    if self.notebook_saved_conns.get_current_page()==1:
      self.new_conn()
    self.window.destroy()
    
    
  def init_db(self):
    log_conn = get_log_conn()
    log_conn = sqlite3.connect( os.path.join(os.path.expanduser('~'),'.desql.db') )
    cur = log_conn.cursor()
    cur.execute('create table if not exists connections(conn_id VARCHAR, db_type VARCHAR, server VARCHAR, port int, '+
                'username VARCHAR, password VARCHAR, schema VARCHAR, ssh_tunnel VARCHAR, count int)')
    cur.close()
    log_conn.commit()
    

  def new_conn(self, conn_id=None, db_type=None, server=None, port=None, username=None, password=None, schema=None, ssh_tunnel=None):
    if not db_type: db_type = self.connection_type.get_active_text()
    if not server: server = self.xml.get_widget('server').get_text()
    if not port: port = self.xml.get_widget('port').get_text()
    if not username: username = self.xml.get_widget('username').get_text()
    if not password: password = self.xml.get_widget('password').get_text()
    if not schema: schema = self.xml.get_widget('schema').get_text()
    if not ssh_tunnel: ssh_tunnel = self.xml.get_widget('ssh_tunnel').get_text()

    tmp_page = gtk.Alignment(xalign=0.5, yalign=0.5, xscale=0.0, yscale=0.0)
    hbox = gtk.HBox()
    hbox.add(gtk.Label('Please wait: Your connection is being established...'))
    cancel = gtk.Button(stock=gtk.STOCK_CANCEL)
    cancel.connect('clicked', lambda *_: self.main.close_tab(tmp_page))
    hbox.add(cancel)
    tmp_page.add(hbox)
    tmp_label = gtk.Label('connecting...')
    self.main.notebook.append_page(tmp_page, tab_label=tmp_label)
    self.main.no_conns_bar.set_visible(False)
    self.main.notebook.set_visible(True)
    tmp_page.show_all()
    tmp_label.show_all()

    def callback(conn_id, conn):
      with gtk.gdk.lock:
        if self.main.notebook.page_num(tmp_page)==-1: return
        hbox = gtk.HBox()
        hbox.set_spacing(8)
        label = gtk.Label()
        label.set_markup('<i>server:</i> %s\n<i>user:</i> %s'%(pango_escape(server),pango_escape(username)))
        hbox.add(label)
        img = gtk.image_new_from_stock(gtk.STOCK_CONNECT, gtk.ICON_SIZE_MENU)
        img.set_tooltip_text('Connection to database is active')
        hbox.add(img)
        c = gui_conn.Conn(self.main, self.notebook, conn, db_type, conn_id, schema)
        page = c.node()
        self.conns.append(c)
        self.notebook.append_page(page, tab_label=hbox)
        self.notebook.set_tab_reorderable(page, True)
        hbox.show_all()
        c.show_all()
        self.notebook.set_current_page(-1)
        self.main.notebook.remove_page(self.main.notebook.page_num(tmp_page))
    def callback_error(s):
      with gtk.gdk.lock:
        self.main.close_tab(tmp_page)
        show_simple_info_dialog('<b>Could Not Connect to Database</b>\n\n' + pango_escape(s))
    create_conn(conn_id=conn_id, db_type=db_type, server=server, port=port, username=username, password=password, 
      schema=schema, ssh_tunnel=ssh_tunnel, callback=callback, callback_error=callback_error)




